ALTER   PROC sp_create_packing_slip_inventory_lot (
@part_number varchar(50),
@lot_number varchar(50),
@qty_in int,
@qty_balance int,
@date_in datetime,
@date_out datetime,
@inventory_type varchar(50),
@vendor_name varchar(50),
@date_code varchar(50),
@vendor_lot varchar(500),
@lot_comments varchar(1000),
@fab_lot_number varchar(50),
@return_lot_id int OUTPUT
)
AS
BEGIN
	DECLARE @temp_product_type varchar(50)
	DECLARE @temp_process_step_id bigint
	DECLARE @parent_product_id int
	DECLARE @location_id int
	DECLARE @lot_id int
	DECLARE @normal_cost float
	DECLARE @inv_lot_id int
	DECLARE @lot_entered_by bit
	DECLARE @lot_already_exists bit
	DECLARE @qty_pulled int
	DECLARE @LOT_CURRENT_STATUS VARCHAR(50)

	set @lot_already_exists = 1
	set @qty_pulled = ( @qty_in - ISNULL( @qty_balance, 0) ) 
	IF ( ( @fab_lot_number is null ) OR ( @fab_lot_number = '' ) )
	BEGIN
		set @fab_lot_number = @lot_number
    END
	IF ( ( @vendor_lot is null ) OR ( @vendor_lot = '' ) )
	BEGIN
		set @vendor_lot = @lot_number
        END

	SELECT @temp_product_type = ISNULL(p_type,'CHIP') FROM Product WHERE Part_Number = @part_number and P_Marketing_Part_Number is not null

	IF ( @temp_product_type = 'CHIP' )
	BEGIN
		SELECT @temp_process_step_id = ProcessStep_ID FROM Product WHERE Part_Number = @part_number AND P_Marketing_Part_Number IS NOT NULL
	END
	ELSE
	BEGIN
		SELECT @parent_product_id = P_Parent_Product_ID FROM Product WHERE Part_Number = @part_number AND P_Marketing_Part_Number IS NOT NULL

		IF ( @parent_product_id = -1)
		BEGIN
			SELECT @temp_process_step_id = ProcessStep_ID FROM Product WHERE Part_Number =  @part_number and p_marketing_Part_Number is not null
		END
		ELSE
		BEGIN
			SELECT @temp_process_step_id = processStep_ID FROM product WHERE Product_id = ( SELECT P_Parent_Product_ID FROM product WHERE Part_Number = @part_number and p_marketing_Part_Number is not null )
		END
	END

	SELECT @location_id = Location_ID FROM Location WHERE L_Type = @inventory_type AND L_Vendor_Name = @vendor_name

	SELECT @lot_id = Lot_ID, @lot_entered_by = ISNULL(Lot_Entered_By,0) FROM Lot_Table WHERE ProcessStep_ID = @temp_process_step_id AND Location_ID = @location_id AND Lot_Number = @lot_number

	IF @lot_id IS NULL
	BEGIN
		set @lot_already_exists = 1

		SELECT @normal_cost = PSLT_Cost_Normal FROM Process_Step_Location_Table WHERE ProcessStep_ID = @temp_process_step_id AND Location_ID = @location_id

		INSERT INTO Lot_Table (ProcessStep_ID, Location_ID, Lot_Number, Fab_Lot_Number, Qty_In, Vendor_Lot_Number, Lot_Category, Date_In, Lot_Date_Code, Lot_Cost, Lot_Current_Status, Timestamp, Lot_Comments, Lot_Entered_By, Lot_Drop_Out ) 
		VALUES (@temp_process_step_id, @location_id, @lot_number, @fab_lot_number, @qty_in, @vendor_lot, 'PROD', @date_in, @date_code, (@normal_cost * @qty_in), 'Running', CURRENT_TIMESTAMP, @lot_comments, 0, 0 )
		
		SELECT @lot_id = @@IDENTITY
	END
	ELSE
	BEGIN
		set @lot_already_exists = 0	
		
		SELECT @LOT_CURRENT_STATUS = @LOT_CURRENT_STATUS FROM LOT_TABLE WHERE LOT_ID = @LOT_ID

		IF @LOT_CURRENT_STATUS != 'Close'
		BEGIN			
			UPDATE LOT_TABLE SET QTY_IN = (QTY_IN + @qty_in), Date_In = @date_in, Date_Out = @date_out, Timestamp = CURRENT_TIMESTAMP, Vendor_Lot_Number = @vendor_lot WHERE LOT_ID = @lot_id
		end
		else
		begin

			UPDATE LOT_TABLE SET QTY_IN = @qty_in, QTY_OUT = null, Date_In = @date_in, Date_Out = @date_out, Timestamp = CURRENT_TIMESTAMP, Vendor_Lot_Number = @vendor_lot WHERE LOT_ID = @lot_id
		end
	END

	INSERT INTO PACKINGSLIP_DATA_ENTRY_LOT_TABLE( LOT_ID, PROCESSSTEP_ID, LOCATION_ID, LOT_NUMBER, DATECODE, QTY_IN, QTY_PULLED, DATE_IN, LOT_ALREADY_EXISTS, TIMESTAMP )
	VALUES( @LOT_ID, @temp_process_step_id, @location_id, @lot_number, @date_code, @qty_in, @qty_pulled, @date_in, @lot_already_exists, CURRENT_TIMESTAMP )
	 

	SELECT @inv_lot_id = Lot_ID FROM Inventory_Lot WHERE lot_id = @lot_id
	IF @inv_lot_id IS NULL
	BEGIN
		/*
		 We are Inserting the Balance as Qty In for Board, as we are reducing the QtyIn and Balance for
		 Boards when we change the status of the PS to Shipped.
		 Changing of PS status to shipped is called after this Proc is called
		*/
		IF ( @temp_product_type = 'BOARD' OR @temp_product_type = 'KIT' )
		BEGIN --INSERTING REJECTED_QTY=0 AND TESTED_QTY = QTY_IN
			INSERT INTO Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Balance, IL_Rejected_Qty, IL_Test_Qty, IL_Date_In, IL_Date_Out, Timestamp, IL_Vendor_Lot_Number) VALUES (@lot_id, @qty_in, @qty_in, 0, @qty_in, @date_in, @date_out, CURRENT_TIMESTAMP, @vendor_lot)
		END
		ELSE
		BEGIN
			INSERT INTO Inventory_Lot (Lot_ID, IL_Qty_In, IL_Qty_Balance, IL_Date_In, IL_Date_Out, Timestamp, IL_Vendor_Lot_Number) VALUES (@lot_id, @qty_in, @qty_in, @date_in, @date_out, CURRENT_TIMESTAMP, @vendor_lot)
		END
	END
	ELSE IF ( @inv_lot_id IS NOT NULL AND @lot_entered_by = 0 )
	BEGIN
		DECLARE @old_qty_in int
  		DECLARE @old_balance_qty int
		DECLARE @pulled_qty int
		DECLARE @old_test_qty INT

		SELECT @old_balance_qty = IL_Qty_Balance, @old_qty_In = LT.Qty_In, @old_test_qty = IL.IL_Test_Qty FROM Inventory_Lot IL, LOT_TABLE LT WHERE LT.Lot_ID = @inv_lot_id AND LT.LOT_ID = IL.LOT_ID
		SELECT @pulled_qty = ISNULL( SUM(CSLNT.CSLNT_Qty_Pull), 0 ) FROM Customer_Shipping_Lot_Number_Table CSLNT INNER JOIN Customer_Shipping_Line_Item_Table CSLIT ON CSLNT.CSLIT_ID = CSLIT.CSLIT_ID INNER JOIN Customer_Shipping_Table CST ON CSLIT.CustomerShipping_ID = CST.CustomerShipping_ID AND (CST.CST_Status = 'ISSUED') AND (CSLNT.CSLNT_Qty_Pull > 0) AND (CSLNT.Lot_ID = @inv_lot_id )

		UPDATE Inventory_Lot SET IL_Date_In = @date_in, IL_Date_Out = @date_out, Timestamp = CURRENT_TIMESTAMP, IL_Vendor_Lot_Number = @vendor_lot WHERE Lot_ID = @inv_lot_id
		
		IF ( @temp_product_type = 'BOARD' OR @temp_product_type = 'KIT' )
		BEGIN --UPDATING TESTED_QTY = @old_tested_qty + QTY_IN
			UPDATE Inventory_Lot SET IL_Qty_In = (@old_qty_In + @qty_in), IL_Qty_Balance = @qty_in, IL_Test_Qty = (@old_test_qty + @qty_in) WHERE Lot_ID = @inv_lot_id
		END
		ELSE
		BEGIN

			IF @LOT_CURRENT_STATUS != 'Close'
			BEGIN	
				UPDATE Inventory_Lot SET IL_Qty_In = (@old_qty_In + @qty_in), IL_Qty_Balance = @qty_in WHERE Lot_ID = @inv_lot_id
			END
			ELSE
			BEGIN
				UPDATE Inventory_Lot SET IL_Qty_In =  @qty_in, IL_Qty_Balance = @qty_in WHERE Lot_ID = @inv_lot_id
			END
		END
	END

	if @lot_id IS NOT NULL AND @lot_id > 0
	BEGIN
		SELECT @return_lot_id = @lot_id
	END
	ELSE
	BEGIN
		SELECT @return_lot_id = -1
	END
	SELECT @return_lot_id
	RETURN

END
